package vn.com.bookstore.daoImpl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import vn.com.bookstore.dao.BookDao;
import vn.com.bookstore.domain.model.Book;
import vn.com.bookstore.domain.model.PagingInfo;
import vn.com.bookstore.domain.model.SearchResult;
import vn.com.bookstore.utils.DaoHelper;
import vn.com.bookstore.utils.DataHelper;

@Repository
public class BookDaoImpl extends DaoHelper<Book> implements BookDao {

	@Override
	public void addNewBook(Book book) {
		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO book (id, name, title, publisher_id, book_type_id, price, date_publish, status, active, discount, number_republish, short_description, long_description, is_hot, like_number, create_date, url_pdf_file, url_image_file, author) ");
		sql.append("VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		Object[] params = new Object[] { book.getId(), book.getName(),
				book.getTitle(), book.getPublisherId(), book.getBookTypeId(),
				book.getPrice(), book.getDatePublish(), book.getStatus(),
				book.getActive(), book.getDiscount(),
				book.getNumberRepublish(), book.getLongDescription(),
				book.getShortDescription(), book.getIsHot(),
				book.getLikeNumber(), book.getCreateDate(),
				book.getUrlPdfFile(), book.getUrlImageFile(), book.getAuthor() };
		insert(sql.toString(), params);

	}
	
	@Override
	public void editBook(Book book) {
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE book SET name = ?, title = ?, publisher_id = ?, book_type_id = ?, price = ?, date_publish = ?, ");
		sql.append("status = ?, active = ?, discount = ?, number_republish = ?, short_description = ?, long_description = ?, is_hot = ?,");
		sql.append(" like_number = ?, create_date = ?, url_pdf_file = ?, url_image_file = ?, author = ? ");
		sql.append("WHERE id = ?");
		Object[] params = new Object[] { book.getName(),
				book.getTitle(), book.getPublisherId(), book.getBookTypeId(),
				book.getPrice(), book.getDatePublish(), book.getStatus(),
				book.getActive(), book.getDiscount(),
				book.getNumberRepublish(), book.getLongDescription(),
				book.getShortDescription(), book.getIsHot(),
				book.getLikeNumber(), book.getCreateDate(),
				book.getUrlPdfFile(), book.getUrlImageFile(), book.getAuthor(), book.getId() };
		insert(sql.toString(), params);
		
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Book> getTop10BookByCategory(String bookType) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT B.id, B.name, B.title, B.book_type_id, B.discount, B.price, B.short_description, B.url_pdf_file, B.url_image_file ");
		sql.append(" FROM book B left join book_type BT on B.book_type_id = BT.id ");
		sql.append(" inner join publisher p on B.publisher_id = p.id");
		sql.append(" WHERE BT.name = ? AND B.active = 1 AND p.active = 1 ");
		Object[] param = new Object[] { bookType };
		SearchResult<Book> result = queryPaging(sql.toString(), param,
				new BookRowMapper(), new PagingInfo(10, 1));
		return result.getSearchResult();
	}

	@SuppressWarnings("rawtypes")
	class BookRowMapper implements RowMapper {

		@Override
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			Book book = new Book();
			book.setId(rs.getInt("id"));
			book.setName(rs.getString("name"));
			book.setTitle(rs.getString("title"));
			book.setBookTypeId(rs.getInt("book_type_id"));
			book.setPrice(rs.getDouble("price"));
			book.setShortDescription(rs.getString("short_description"));
			book.setUrlImageFile(rs.getString("url_image_file"));
			book.setUrlPdfFile(rs.getString("url_pdf_file"));
			book.setDiscount(rs.getDouble("discount"));
			try {
				book.setPublisherId(rs.getInt("publisher_id"));
			} catch (Exception e) {
			}

			try {
				book.setLongDescription(rs.getString("long_description"));
			} catch (Exception e) {
			}

			try {
				book.setLikeNumber(rs.getInt("like_number"));
			} catch (Exception e) {
			}

			try {
				book.setAuthor(rs.getString("author"));
			} catch (Exception e) {
			}

			try {
				book.setNumberRepublish(rs.getInt("number_republish"));
			} catch (Exception e) {
			}
			try {
				book.setDatePublish(rs.getDate("date_publish"));
			} catch (Exception e) {
			}
			try {
				book.setStatus(rs.getString("status"));
			} catch (Exception e) {
			}
			try {
				book.setActive(rs.getShort("active"));
			} catch (Exception e) {
			}
			try {
				book.setCreateDate(rs.getDate("create_date"));
			} catch (Exception e) {
			}
			try {
				book.setIsHot(rs.getBoolean("is_hot"));
			} catch (Exception e) {
			}

			return book;
		}

	}

	@SuppressWarnings("unchecked")
	@Override
	public Book getBookById(int id) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM book B WHERE B.id = ? AND B.active = 1");
		Object[] param = new Object[] { id };
		List<Book> result = query(sql.toString(), param, new BookRowMapper()).getSearchResult();
		if(result.isEmpty()) {
			return new Book();
		}
		return result.get(0);
	}

	@SuppressWarnings("unchecked")
	@Override
	public SearchResult<Book> getRelatedBook(int categoryId, int id) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT B.id, B.name, B.title, B.discount, B.book_type_id, B.price, B.short_description, B.url_pdf_file, B.url_image_file ")
		.append(" FROM book B ") 
		.append(" inner join publisher p on B.publisher_id = p.id")
		.append(" WHERE B.book_type_id = ? AND B.id <> ? AND p.active = 1 AND B.active = 1 ");
		Object[] param = new Object[]{categoryId, id};
		return queryPaging(sql.toString(), param, new BookRowMapper(), new PagingInfo(4, 1));
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Book> getListBookByPublisher(int publisherId) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM book B ");
		sql.append(" inner join publisher p on B.publisher_id = p.id");
		sql.append("WHERE B.publisher_id = ? AND p.active = 1 ");
		Object[] param = new Object[] { publisherId };
		SearchResult<Book> bookList = query(sql.toString(), param, new BookRowMapper());
		return bookList.getSearchResult();
	}

	@SuppressWarnings("unchecked")
	@Override
	public SearchResult<Book> getBooks(PagingInfo pagingInfo, String keyword, String typeSearch, int publishId) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT b.* FROM book b inner join book_type bt")
		.append(" on b.book_type_id = bt.id ")
		.append(" inner join publisher p on B.publisher_id = p.id")
		.append(" WHERE b.active = 1 AND bt.active = 1 AND publisher_id = ").append(publishId)
		.append(" AND p.active = 1 ");
		
		String typeSearchKey = "";
		if(StringUtils.isNotEmpty(typeSearch)){
			if(typeSearch.equals(DataHelper.BookTypeSearch.BOOK_TYPE_SEARCH_NAME.getCode())){
				typeSearchKey = "b.name";
			}else if(typeSearch.equals(DataHelper.BookTypeSearch.BOOK_TYPE_SEARCH_AUTHOR.getCode())){
				typeSearchKey = "b.author";
			}else if(typeSearch.equals(DataHelper.BookTypeSearch.BOOK_TYPE_SEARCH_CATEGORY.getCode())){
				typeSearchKey = "bt.name";
			}
		}
		
		if (StringUtils.isNotEmpty(keyword) || StringUtils.isNotEmpty(typeSearch)) {
			sql.append("AND (");
			String[] keyStrings = keyword.split(" ");
			for (int i = 0; i < keyStrings.length; i++) {
				String s = "'%" + keyStrings[i] + "%'";
				if (i == 0) {
					sql.append(" " + typeSearchKey + " LIKE " + s );
				} else {
					sql.append(" OR " + typeSearchKey + " LIKE " + s );
				}
			}
			sql.append(")");
		}
		sql.append(" ORDER BY b.create_date DESC");
		return queryPaging(sql.toString(), null, new BookRowMapper(),
				pagingInfo);
	}

	@SuppressWarnings("unchecked")
	@Override
	public SearchResult<Book> getBooksByBookTypeId(PagingInfo pagingInfo,
			int bookTypeId) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT b.* FROM book b inner join book_type bt  on b.book_type_id = bt.id ")
		.append(" inner join publisher p on b.publisher_id = p.id")
		.append(" WHERE b.active = 1 and bt.active = 1 and bt.id=? AND p.active = 1 ")
		.append(" ORDER BY b.create_date DESC");
		Object[] param = new Object[]{bookTypeId};
		return queryPaging(sql.toString(), param, new BookRowMapper(), pagingInfo);
	}

	@Override
	public void deleteBook(int bookId) {
		String sql= "Update book set active = 0 where id = ?";
		Object[] params = new Object[] {bookId};
		insert(sql, params);
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public SearchResult<Book> getBooksBykeyword(PagingInfo pagingInfo, String keyword) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT b.* FROM book b inner join book_type bt ")
		.append(" on b.book_type_id = bt.id ")
		.append(" inner join publisher p on b.publisher_id = p.id ")
		.append(" WHERE b.active = 1 AND bt.active = 1 AND p.active = 1 ");
		if (StringUtils.isNotEmpty(keyword)) {
			sql.append("AND (");
			String[] keyStrings = keyword.split(" ");
			for (int i = 0; i < keyStrings.length; i++) {
				String s = "'%" + keyStrings[i] + "%'";
				if (i == 0) {
					sql.append(" b.name LIKE ").append(s).append(" OR bt.name LIKE ").append(s)
					.append("OR p.name LIKE ").append(s);
				} else {
					sql.append(" OR b.name LIKE ").append(s).append(" OR bt.name LIKE ").append(s)
					.append("OR p.name LIKE ").append(s);
					
				}
			}
			sql.append(")");
		}
		sql.append(" ORDER BY b.create_date DESC");
		return queryPaging(sql.toString(), null, new BookRowMapper(), pagingInfo);
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public SearchResult<Book> getTop10BookOfTransaction(String role, int publisherId) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT b.* , count(b.id) CountBook ");
		sql.append("FROM transaction t, book b, publisher p, customer c ");
		sql.append("WHERE t.customer_id = c.id ");
		sql.append("AND t.book_id = b.id ");
		sql.append("AND b.active = 1 ");
		
		if(DataHelper.UserRole.ROLE_PUBLISHER.getCode().equals(role)){
			sql.append("AND b.publisher_id = " + publisherId + " ");
		}
		
		PagingInfo pagingInfo = new PagingInfo(10, 1);
		
		sql.append(" GROUP BY b.id ");
		sql.append("ORDER BY count(b.id) DESC ");
		
		SearchResult<Book> searchResult = queryPaging(sql.toString(), null, new BookRowMapper(),
				pagingInfo);
		return searchResult;
	}

}
